
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, 2012 and Azure
-- --------------------------------------------------
-- Date Created: 02/27/2014 09:45:36
-- Generated from EDMX file: C:\working\tfs\producttransfer\ProductTransfer\DatabaseCore\DbModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [ExportDB];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_tbm_User_tbm_Role]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[tbl_User] DROP CONSTRAINT [FK_tbm_User_tbm_Role];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[DataProduct]', 'U') IS NOT NULL
    DROP TABLE [dbo].[DataProduct];
GO
IF OBJECT_ID(N'[dbo].[tbl_ExcelFile]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tbl_ExcelFile];
GO
IF OBJECT_ID(N'[dbo].[tbl_ExportRate]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tbl_ExportRate];
GO
IF OBJECT_ID(N'[dbo].[tbl_ExportRateHistory]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tbl_ExportRateHistory];
GO
IF OBJECT_ID(N'[dbo].[tbl_GenerateNo]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tbl_GenerateNo];
GO
IF OBJECT_ID(N'[dbo].[tbl_Order]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tbl_Order];
GO
IF OBJECT_ID(N'[dbo].[tbl_OrderDetail]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tbl_OrderDetail];
GO
IF OBJECT_ID(N'[dbo].[tbl_OrderDetailHistory]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tbl_OrderDetailHistory];
GO
IF OBJECT_ID(N'[dbo].[tbl_OrderHistory]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tbl_OrderHistory];
GO
IF OBJECT_ID(N'[dbo].[tbl_OrderStatus]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tbl_OrderStatus];
GO
IF OBJECT_ID(N'[dbo].[tbl_ProductAvailable]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tbl_ProductAvailable];
GO
IF OBJECT_ID(N'[dbo].[tbl_ProductAvailableHistory]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tbl_ProductAvailableHistory];
GO
IF OBJECT_ID(N'[dbo].[tbl_Queue]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tbl_Queue];
GO
IF OBJECT_ID(N'[dbo].[tbl_Role]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tbl_Role];
GO
IF OBJECT_ID(N'[dbo].[tbl_Transaction]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tbl_Transaction];
GO
IF OBJECT_ID(N'[dbo].[tbl_User]', 'U') IS NOT NULL
    DROP TABLE [dbo].[tbl_User];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'tbl_ExcelFile'
CREATE TABLE [dbo].[tbl_ExcelFile] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [PathName] nvarchar(50)  NOT NULL,
    [CreateBy] nvarchar(50)  NOT NULL,
    [CreateDate] datetime  NOT NULL
);
GO

-- Creating table 'tbl_ExportRate'
CREATE TABLE [dbo].[tbl_ExportRate] (
    [Id] int  NOT NULL,
    [Rate] tinyint  NOT NULL,
    [ValidFrom] datetime  NULL,
    [ValidTo] datetime  NULL,
    [UpdateBy] nvarchar(50)  NOT NULL,
    [UpdateDate] datetime  NOT NULL
);
GO

-- Creating table 'tbl_ExportRateHistory'
CREATE TABLE [dbo].[tbl_ExportRateHistory] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Rate] tinyint  NOT NULL,
    [ValidFrom] datetime  NULL,
    [ValidTo] datetime  NULL,
    [ProductId] nvarchar(50)  NOT NULL,
    [UpdateBy] nvarchar(50)  NOT NULL,
    [UpdateDate] datetime  NOT NULL
);
GO

-- Creating table 'tbl_GenerateNo'
CREATE TABLE [dbo].[tbl_GenerateNo] (
    [Key] nvarchar(50)  NOT NULL,
    [RunningNo] int  NOT NULL
);
GO

-- Creating table 'tbl_Order'
CREATE TABLE [dbo].[tbl_Order] (
    [OrderNo] nvarchar(50)  NOT NULL,
    [RefOrderNo] nvarchar(50)  NULL,
    [OrderDate] datetime  NOT NULL,
    [InvoiceNo] nvarchar(50)  NULL,
    [PortOfShipment] nvarchar(50)  NULL,
    [PortOfDestination] nvarchar(50)  NULL,
    [BuyerCode] nvarchar(50)  NULL,
    [BuyerName] nvarchar(50)  NULL,
    [ShipmentNote] nvarchar(max)  NULL,
    [ConsigneeName] nvarchar(50)  NULL,
    [Payment] nvarchar(50)  NULL,
    [PONo] nvarchar(50)  NULL,
    [Location] nvarchar(50)  NULL,
    [TotalQty] int  NULL,
    [TotalPackage] int  NULL,
    [TotalAmount] decimal(10,4)  NULL,
    [TotalWeight] decimal(10,4)  NULL,
    [Status] int  NULL,
    [CreateBy] nvarchar(50)  NULL,
    [CreateDate] datetime  NULL,
    [UpdateBy] nvarchar(50)  NULL,
    [UpdateDate] datetime  NULL
);
GO

-- Creating table 'tbl_OrderDetail'
CREATE TABLE [dbo].[tbl_OrderDetail] (
    [OrderNo] nvarchar(50)  NOT NULL,
    [ItemNo] int  NOT NULL,
    [ItemId] nvarchar(50)  NULL,
    [ItemName] nvarchar(50)  NULL,
    [ProductId] nvarchar(50)  NULL,
    [ProductName] nvarchar(50)  NULL,
    [Qty] int  NOT NULL,
    [UnitPrice] decimal(10,2)  NULL,
    [Amount] decimal(10,2)  NULL,
    [PcsPerBox] int  NULL,
    [Package] int  NULL,
    [WeightPerUnit] decimal(10,4)  NULL,
    [Weight] decimal(10,4)  NULL,
    [MaterialNo] nvarchar(50)  NULL,
    [Plant] nvarchar(4)  NULL,
    [CreateBy] nvarchar(50)  NOT NULL,
    [CreateDate] datetime  NOT NULL,
    [UpdateBy] nvarchar(50)  NOT NULL,
    [UpdateDate] datetime  NOT NULL,
    [ReceivedQty] int  NOT NULL,
    [RemainingQty] int  NOT NULL
);
GO

-- Creating table 'tbl_OrderStatus'
CREATE TABLE [dbo].[tbl_OrderStatus] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'tbl_ProductAvailableHistory'
CREATE TABLE [dbo].[tbl_ProductAvailableHistory] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ProductCode] nvarchar(50)  NOT NULL,
    [OrderNo] nvarchar(50)  NOT NULL,
    [Qty] int  NOT NULL
);
GO

-- Creating table 'tbl_Queue'
CREATE TABLE [dbo].[tbl_Queue] (
    [ProductCode] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'tbl_Role'
CREATE TABLE [dbo].[tbl_Role] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'tbl_User'
CREATE TABLE [dbo].[tbl_User] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [UserName] nvarchar(50)  NOT NULL,
    [Password] nvarchar(50)  NOT NULL,
    [Email] nvarchar(50)  NOT NULL,
    [FullName] nvarchar(100)  NOT NULL,
    [Role] int  NOT NULL,
    [Telephone] nvarchar(50)  NULL,
    [Fax] nvarchar(50)  NULL,
    [PinCode] nvarchar(4)  NULL,
    [Active] bit  NOT NULL
);
GO

-- Creating table 'DataProduct'
CREATE TABLE [dbo].[DataProduct] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Prod_no_7] nvarchar(50)  NOT NULL,
    [Prod_no_18] nvarchar(50)  NULL,
    [Unit_price] decimal(18,2)  NULL,
    [Unit_weight] decimal(18,3)  NULL,
    [Qty_Box] int  NULL,
    [Box_Size] nvarchar(50)  NULL,
    [Dia] nvarchar(50)  NULL,
    [Class] nvarchar(50)  NULL,
    [Discount] int  NULL,
    [mprodcate4] nvarchar(50)  NULL
);
GO

-- Creating table 'tbl_OrderDetailHistory'
CREATE TABLE [dbo].[tbl_OrderDetailHistory] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [OrderNo] nvarchar(50)  NOT NULL,
    [ItemNo] int  NOT NULL,
    [ItemCode] nvarchar(50)  NULL,
    [ProductCode] nvarchar(50)  NULL,
    [QtyFrom] int  NULL,
    [QtyTo] int  NULL,
    [StockQty] int  NULL,
    [MaterialNo] nvarchar(50)  NULL,
    [Plant] nvarchar(4)  NULL,
    [UpdateBy] nvarchar(50)  NULL,
    [UpdateDate] datetime  NULL
);
GO

-- Creating table 'tbl_Transaction'
CREATE TABLE [dbo].[tbl_Transaction] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ProductCode] nvarchar(50)  NOT NULL,
    [OrderNo] nvarchar(50)  NOT NULL,
    [StockNo] nvarchar(50)  NULL,
    [PlantFrom] nvarchar(4)  NULL,
    [PlantTo] nvarchar(4)  NULL,
    [Qty] int  NULL,
    [MovementType] nvarchar(3)  NULL,
    [UpdateBy] nvarchar(50)  NOT NULL,
    [UpdateDate] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'tbl_OrderHistory'
CREATE TABLE [dbo].[tbl_OrderHistory] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [OrderNo] nvarchar(50)  NOT NULL,
    [TotalQty] int  NOT NULL,
    [TotalPackage] int  NOT NULL,
    [TotalAmount] decimal(10,4)  NOT NULL,
    [TotalWeight] decimal(10,4)  NOT NULL,
    [Status] int  NOT NULL,
    [UpdateBy] nvarchar(50)  NOT NULL,
    [UpdateDate] datetime  NOT NULL
);
GO

-- Creating table 'tbl_ProductAvailable'
CREATE TABLE [dbo].[tbl_ProductAvailable] (
    [MaterialNo] nvarchar(50)  NOT NULL,
    [OrderNo] nvarchar(50)  NOT NULL,
    [RemainingQty] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'tbl_ExcelFile'
ALTER TABLE [dbo].[tbl_ExcelFile]
ADD CONSTRAINT [PK_tbl_ExcelFile]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'tbl_ExportRate'
ALTER TABLE [dbo].[tbl_ExportRate]
ADD CONSTRAINT [PK_tbl_ExportRate]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'tbl_ExportRateHistory'
ALTER TABLE [dbo].[tbl_ExportRateHistory]
ADD CONSTRAINT [PK_tbl_ExportRateHistory]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Key] in table 'tbl_GenerateNo'
ALTER TABLE [dbo].[tbl_GenerateNo]
ADD CONSTRAINT [PK_tbl_GenerateNo]
    PRIMARY KEY CLUSTERED ([Key] ASC);
GO

-- Creating primary key on [OrderNo] in table 'tbl_Order'
ALTER TABLE [dbo].[tbl_Order]
ADD CONSTRAINT [PK_tbl_Order]
    PRIMARY KEY CLUSTERED ([OrderNo] ASC);
GO

-- Creating primary key on [OrderNo], [ItemNo] in table 'tbl_OrderDetail'
ALTER TABLE [dbo].[tbl_OrderDetail]
ADD CONSTRAINT [PK_tbl_OrderDetail]
    PRIMARY KEY CLUSTERED ([OrderNo], [ItemNo] ASC);
GO

-- Creating primary key on [Id] in table 'tbl_OrderStatus'
ALTER TABLE [dbo].[tbl_OrderStatus]
ADD CONSTRAINT [PK_tbl_OrderStatus]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'tbl_ProductAvailableHistory'
ALTER TABLE [dbo].[tbl_ProductAvailableHistory]
ADD CONSTRAINT [PK_tbl_ProductAvailableHistory]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [ProductCode] in table 'tbl_Queue'
ALTER TABLE [dbo].[tbl_Queue]
ADD CONSTRAINT [PK_tbl_Queue]
    PRIMARY KEY CLUSTERED ([ProductCode] ASC);
GO

-- Creating primary key on [Id] in table 'tbl_Role'
ALTER TABLE [dbo].[tbl_Role]
ADD CONSTRAINT [PK_tbl_Role]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'tbl_User'
ALTER TABLE [dbo].[tbl_User]
ADD CONSTRAINT [PK_tbl_User]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'DataProduct'
ALTER TABLE [dbo].[DataProduct]
ADD CONSTRAINT [PK_DataProduct]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'tbl_OrderDetailHistory'
ALTER TABLE [dbo].[tbl_OrderDetailHistory]
ADD CONSTRAINT [PK_tbl_OrderDetailHistory]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'tbl_Transaction'
ALTER TABLE [dbo].[tbl_Transaction]
ADD CONSTRAINT [PK_tbl_Transaction]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'tbl_OrderHistory'
ALTER TABLE [dbo].[tbl_OrderHistory]
ADD CONSTRAINT [PK_tbl_OrderHistory]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [MaterialNo], [OrderNo] in table 'tbl_ProductAvailable'
ALTER TABLE [dbo].[tbl_ProductAvailable]
ADD CONSTRAINT [PK_tbl_ProductAvailable]
    PRIMARY KEY CLUSTERED ([MaterialNo], [OrderNo] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Role] in table 'tbl_User'
ALTER TABLE [dbo].[tbl_User]
ADD CONSTRAINT [FK_tbm_User_tbm_Role]
    FOREIGN KEY ([Role])
    REFERENCES [dbo].[tbl_Role]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_tbm_User_tbm_Role'
CREATE INDEX [IX_FK_tbm_User_tbm_Role]
ON [dbo].[tbl_User]
    ([Role]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------